package managerSIP;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

// All methods in this class are made by SQL Connection
public class Members {

    /**
     * @uml.property  name="conn"
     */
    private Connection conn = null;

    public Members()
    {	
	// Access file
	File file = new File("mysql.properties");    
	Properties props = new Properties();
	FileInputStream fis = null;
	try 
	{
	    fis = new FileInputStream(file);
	    //Read properties in file mysql.properties
	    props.load(fis); 
	    fis.close();
	}
	catch (IOException ex) {
	    System.out.println(ex.getMessage());
	    ex.printStackTrace();
	}

	// Connect to Mysql server	
	try
	{
	    String userName = props.getProperty("mysql.username");
	    String password = props.getProperty("mysql.password");
	    String server = props.getProperty("mysql.server");
	    String db = props.getProperty("mysql.db");
	    String url = "jdbc:mysql://"+ server + "/" + db;
	    Class.forName ("com.mysql.jdbc.Driver").newInstance ();
	    conn = DriverManager.getConnection (url,userName, password);
	    System.out.println ("Database connection established");
	}
	catch (Exception e)
	{
	    System.err.println ("Cannot connect to database server");
	}
    }

    public int createUser(String name, String context, String host, String secret) throws SQLException
    {
	// Insert user in asterisk table for conference
	Statement stm = conn.createStatement();
	int rs = stm.executeUpdate("INSERT INTO sip_users (name, username, context, host, secret)"
		+ " VALUES" + "('" + name + "','" + name + "','" + context + "','" + host + "','" + secret + "')");
	conn.close();
	return rs;
    }

    public int delUser(String name) throws SQLException
    {
	// Delete user in asterisk table
	Statement stm = conn.createStatement();
	int rs = stm.executeUpdate("DELETE FROM sip_users WHERE name='" + name + "'");
	conn.close();
	return rs;
    }

    public String getInfoUserReg(String name, String field) throws SQLException
    {
	// Get user register for management
	Statement stm = conn.createStatement();
	ResultSet rs = stm.executeQuery("SELECT "+field+" FROM sip_users WHERE name='" + name + "'"); 
	rs.next();
	conn.close();
	return rs.getString(field);	
    }

    public boolean userExists(String name) throws SQLException
    {
	// Get info if user exist in database
	Statement stm = conn.createStatement();
	ResultSet rs = stm.executeQuery("SELECT * FROM sip_users WHERE name='" + name + "'"); 
	rs.next();
	conn.close();
	return true;	
    }

}
